<?php

/**
 * excel文件处理
 * User: Pulingao
 * Date: 2014/11/30
 */
class Excel {

	protected $PHPExcelExport;
	protected $_PHPExcelImport;
	protected $PHPExcelImport;
	protected $FileName;
	protected $Type = 'xlsx';
	protected $sheetCount;
	protected $sheetNames;
	protected $sheetCurrent;
	protected $RowLength;
	protected $ColLength;

	private function getFileName() {
		return $this->FileName ? $this->FileName : 'export';
	}

	private function getType() {
		return ($this->Type == 'xls') ? true : false;  # true表示xls，false表示xlsx
	}

	public function exportHeader() {
		ini_set("max_execution_time", 0);
		ini_set("memory_limit", "2048M");
		ini_set('display_errors', TRUE);
		ini_set('display_startup_errors', TRUE);
		$this->PHPExcelExport = new PHPExcel();
		$this->PHPExcelExport->getProperties()
				->setCreator("Sino")
				->setLastModifiedBy("Sino")
				->setTitle("Sino")
				->setSubject("Sino")
				->setDescription("Sino")
				->setKeywords("Sino")
				->setCategory("Sino");
	}

	public function exportFooter() {
		$realFileName = $this->getType() ? $this->getFileName() . '.xls' : $this->getFileName() . '.xlsx';
		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");
        header('Content-Type: application/vnd.ms-excel; charset=utf-8'); 
		header("Content-Transfer-Encoding:binary");
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition:attachment;filename=' . urlencode($realFileName));
		$objWriter = $this->getType() ? PHPExcel_IOFactory::createWriter($this->PHPExcelExport, 'Excel5') : PHPExcel_IOFactory::createWriter($this->PHPExcelExport, 'Excel2007');
		$objWriter->save('php://output');
		exit();
	}

	public function importBase($file) {
		$readerType = $this->getType() ? 'Excel5' : 'Excel2007';
		$objReader = PHPExcel_IOFactory::createReader($readerType);
		$this->_PHPExcelImport = $objReader->load($file);
		$this->sheetNames = $objReader->listWorksheetNames($file);
		
		//获取sheet总数
		$this->sheetCount = $this->_PHPExcelImport->getSheetCount();
                for ($this->sheetCurrent = 0; $this->sheetCurrent < $this->sheetCount; $this->sheetCurrent++) {
			$this->PHPExcelImport[$this->sheetCurrent] = $this->_PHPExcelImport->getSheet($this->sheetCurrent);
			$this->ColLength[$this->sheetCurrent] = $this->PHPExcelImport[$this->sheetCurrent]->getHighestColumn(); //获取总列数
			$this->RowLength[$this->sheetCurrent] = $this->PHPExcelImport[$this->sheetCurrent]->getHighestRow();  //获取总行数
			
			// 获取真实的非空行数和非空列数
			$i = $this->sheetCurrent;
			$rowMaxIndex = 0;
			$colMaxIndex = 0;
			$this->ColLength[$i] = PHPExcel_Cell::columnIndexFromString($this->ColLength[$i]); // 转化列
			for($rowNum=1;$rowNum<=$this->RowLength[$i];$rowNum++){
				for($colNum=0;$colNum<$this->ColLength[$i];$colNum++){
					$item = $this->PHPExcelImport[$i]->getCellByColumnAndRow($colNum,$rowNum);
					$realRow = self::excelValue($item);
					if ($realRow) {
						$rowMaxIndex = ($rowNum > $rowMaxIndex) ? $rowNum : $rowMaxIndex;
						$colMaxIndex = (($colNum+1) > $colMaxIndex) ? ($colNum+1) : $colMaxIndex;
					}
				}
			}
			$this->RowLength[$i] = $rowMaxIndex;
			$this->ColLength[$i] = $colMaxIndex;
		}
		
		// 防止出现一个空的sheet
		for($i=0;$i<$this->sheetCount;$i++){
			if(!$this->ColLength[$i] && !$this->RowLength[$i]){
				unset($this->RowLength[$i]);
				unset($this->ColLength[$i]);
				unset($this->PHPExcelImport[$i]);
			}
		}
		
		// 单独一个sheet列
		if (count($this->ColLength) == 1) {
			$col = each($this->ColLength);
			$this->sheetCount = 1;
			$this->ColLength = $this->ColLength[$col['key']];
			$this->RowLength = $this->RowLength[$col['key']];
			$this->PHPExcelImport = $this->PHPExcelImport[$col['key']];
		}
	}
	
	/**
	 * 判断单元格是否属于日期类型
	 * @param type $value
	 * @return type
	 */
	public static function isDataTime($value){
		return PHPExcel_Shared_Date::isDateTime($value);
	}

	/**
	 * 解析excel中的时间格式单元格的数据
	 * @param type $date
	 * @param type $unixTime
	 * @param type $timezone
	 * @return boolean
	 */
	public static function excelValue($itemObj) {
		$itemValue = trim($itemObj->getValue());
		$itemType = self::isDataTime($itemObj);
		if(!$itemType){
			return $itemValue;
		}
		return PHPExcel_Shared_Date::ExcelToPHP($itemValue, 'Asia/Shanghai', 'Asia/Shanghai');
	}
	
	/**
	 * 将excel中的字符列转换成数字列
	 * @param type $char
	 * @return int $num
	 */
	public static function Char2Num($char) {
		$num = 0;
		if (!$char) {
			return $num;
		}
		$char = strtoupper($char);
		$charLen = strlen($char);
		if ($charLen > 1) {
			$pre = substr($char, 0, 1);
			$suf = substr($char, 1, 1);
			$num = (ord($pre) - 64) * 26 + (ord($suf) - 64);
		}else{
			$num = ord($char) - 64;
		}
		return $num;
	}
	
	

}
